package gov.nhso.prov.acc.apps.acc6;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

@SuppressWarnings({"rawtypes","deprecation"})
public class ACC60401 extends Utility{
	public String url = "/apps/acc6/ACC60401A.jsp";

	public ACC60401(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		}else if (job.equals("search")){
			this.search(request);
		}else if (job.equals("export")){
			this.export(request, response);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer sb = new StringBuffer();
			List listAllocateNoUc = new ArrayList();
			List listAllocateUc = new ArrayList();
			List list = new ArrayList();
			String type = this.getAttr(request, "type");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String budgetYear = this.getAttr(request, "budgetYear");
			String allocateYear = this.getAttr(request, "allocateYear");
			String provinceId = this.getAttr(request, "provinceId");
			String provTxt= "";
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			allocateBean.setBudgetYear(allocateYear);
			String buffer = "";
			int j = 0;
			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			int iBudgetYear = Integer.parseInt(budgetYear);
			int startBudgetYear = 2;
			int endBudgetYear = 1;
			if (type.equals("0") || type.equals("1")){
				listAllocateUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE NOT LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
				for(int i=0;i<listAllocateUc.size();i++){
					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateUc.get(i);
					sb.delete(0, sb.length());
					if (iBudgetYear <= 2556) {
						sb.setLength(0);
						sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'B' THEN SUM(INCOME_AMOUNT) ELSE 0 END AS B ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
						sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
						}
					} else {
						int iBudgetYearAD = iBudgetYear - 543;
						bean.setSumIncomeAmountTypeB("0");
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('T','P') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(EXPENSE_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(EXPENSE_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
						Map map = dao.findDataSql(sb.toString());
						double expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));

						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('C') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND (TRUNC(CHEQUE_DATE) >= TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(CHEQUE_DATE) <= TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY')) ");
						sb.append("AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
						
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('B','S','F','R','O') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(INCOME_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(INCOME_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						double imcomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
						double remain = imcomeAmount - expenseAmount;
						bean.setSumIncomeAmountTypeB(String.valueOf(remain));
					}
					
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('S','F','R','O') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS S ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeS("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
					}

					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A','P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(EXPENSE_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(EXPENSE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumExpenseAmount("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
					
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'C' THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND (TRUNC(CHEQUE_DATE) >= " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
				}
			}
			if (type.equals("0") || type.equals("2")){
				allocateBean = new Acc6AllocateBean();
				allocateBean.setBudgetYear(allocateYear);
				listAllocateNoUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
				for(int i=0;i<listAllocateNoUc.size();i++){
					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateNoUc.get(i);
					sb.delete(0, sb.length());
					if (iBudgetYear <= 2556) {
						sb.setLength(0);
						sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'B' THEN SUM(INCOME_AMOUNT) ELSE 0 END AS B ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
						sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
						}
					} else {
						int iBudgetYearAD = iBudgetYear - 543;
						bean.setSumIncomeAmountTypeB("0");
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('T','P') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(EXPENSE_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(EXPENSE_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
						Map map = dao.findDataSql(sb.toString());
						double expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));

						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('C') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND (TRUNC(CHEQUE_DATE) <= TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(CHEQUE_DATE) <= TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY')) ");
						sb.append("AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
						
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('B','S','F','R','O') ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(INCOME_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
						sb.append("AND TRUNC(INCOME_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						double imcomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
						double remain = imcomeAmount - expenseAmount;
						bean.setSumIncomeAmountTypeB(String.valueOf(remain));
					}
					
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('S','F','R','O') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS S ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeS("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						if (map.get("S") != null){
							buffer = map.get("S").toString();
						}
						bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(buffer)+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));	
					}
					sb.delete(0, sb.length());
					//sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X  ");
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A','P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X  ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}

					sb.append("AND (TRUNC(EXPENSE_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(EXPENSE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumExpenseAmount("0");
					
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						if (map.get("X") != null){
							buffer = map.get("X").toString();
						}
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(buffer)+ Double.parseDouble(bean.getSumExpenseAmount())));
					}

					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'C' THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND (TRUNC(CHEQUE_DATE) >= " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
				}
			}
		
			/*if (Integer.parseInt(allocateYear) >=  Integer.parseInt(budgetYear)) {} else {
				if (type.equals("0") || type.equals("1")){
					listAllocateUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE NOT LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
					for(int i=0;i<listAllocateUc.size();i++){
						Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateUc.get(i);
						sb.delete(0, sb.length());
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) -  (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS B, ");
						sb.append("0 AS S, ");
						sb.append("0 AS X ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						bean.setSumIncomeAmountTypeS("0");
						bean.setSumExpenseAmount("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
							bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
							bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
						}
					}
				}
				if (type.equals("0") || type.equals("2")){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setBudgetYear(allocateYear);
					listAllocateNoUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
					for(int i=0;i<listAllocateNoUc.size();i++){
						Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateNoUc.get(i);
						sb.delete(0, sb.length());
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) -  (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS B, ");
						sb.append("0 AS S, ");
						sb.append("0 AS X ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						bean.setSumIncomeAmountTypeS("0");
						bean.setSumExpenseAmount("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
							bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
							bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
						}
					}
				}
			}*/
			
			if(listAllocateNoUc.size() < 1 && listAllocateUc.size() < 1){
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("allocateYear", allocateYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("listAllocateNoUc", listAllocateNoUc);
			request.setAttribute("listAllocateUc", listAllocateUc);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private void export(HttpServletRequest request, HttpServletResponse response){
		
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer sb = new StringBuffer();
			List listAllocateNoUc = new ArrayList();
			List listAllocateUc = new ArrayList();
			List list = new ArrayList();
			String type = this.getAttr(request, "type");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			String allocateYear = this.getAttr(request, "allocateYear");
			String provTxt = "";
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			allocateBean.setBudgetYear(allocateYear);
			String buffer = "";
			int j = 0;

			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\""+budgetYear +".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet 0");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFCellStyle cellStyleAlignRigth = wb.createCellStyle();
			cellStyleAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);
			HSSFCell cell6 = row.createCell(5);
			
			sheet.setDefaultColumnWidth((short) 35);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyle);
			cell6.setCellStyle(cellStyle);
			
			cell1.setCellValue("รหัสแจ้งจัดสรรเงินให้คู่สัญญา");
			cell2.setCellValue("ชื่อแจ้งจัดสรรเงินให้คู่สัญญา");
			cell3.setCellValue("ยอดยกมา");
			cell4.setCellValue("จำนวนเงินที่ได้รับจัดสรร");
			cell5.setCellValue("จำนวนเงินที่จ่ายให้หน่วยบริการ");
			cell6.setCellValue("คงเหลือ");
			

			if (type.equals("0") || type.equals("1")){
				listAllocateUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE NOT LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
				for(int i=0;i<listAllocateUc.size();i++){
					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateUc.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'B' THEN SUM(INCOME_AMOUNT) ELSE 0 END AS B ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeB("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
					}
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('S','F','R','O') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS S ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeS("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
					}

					sb.delete(0, sb.length());
					//sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A','P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(EXPENSE_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(EXPENSE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumExpenseAmount("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
					
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'C' THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND (TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
				}
			}
			if (type.equals("0") || type.equals("2")){
				allocateBean = new Acc6AllocateBean();
				allocateBean.setBudgetYear(allocateYear);
				listAllocateNoUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
				for(int i=0;i<listAllocateNoUc.size();i++){
					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateNoUc.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'B' THEN SUM(INCOME_AMOUNT) ELSE 0 END AS B ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeB("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						if (map.get("B") != null){
							buffer = map.get("B").toString();
						}
						bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(buffer)+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));	
					}
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('S','F','R','O') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS S ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumIncomeAmountTypeS("0");
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						if (map.get("S") != null){
							buffer = map.get("S").toString();
						}
						bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(buffer)+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));	
					}
					sb.delete(0, sb.length());
					//sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X  ");
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE IN ('T','A','P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}

					sb.append("AND (TRUNC(EXPENSE_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
					sb.append("AND TRUNC(EXPENSE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					bean.setSumExpenseAmount("0");
					
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						if (map.get("X") != null){
							buffer = map.get("X").toString();
						}
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(buffer)+ Double.parseDouble(bean.getSumExpenseAmount())));
					}

					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN TRANSACTION_TYPE = 'C' THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if(bean.getAllocateApply().equals("N")){
						if (bean.getAllocateCode().endsWith("00")) {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
						} else {
							sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
						}
					} else {
						sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
					}
					sb.append("AND (TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
					sb.append("AND TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + ") ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("GROUP BY TRANSACTION_TYPE ");
					list = dao.listDataSql(sb.toString());
					for (j = 0;j<list.size();j++){
						Map map = (Map) list.get(j);
						bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
					}
				}
			}

			/*if (Integer.parseInt(allocateYear) >=  Integer.parseInt(budgetYear)) {} else {
				if (type.equals("0") || type.equals("1")){
					allocateBean.setBudgetYear(allocateYear);
					listAllocateUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE NOT LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");	
					
					for(int i=0;i<listAllocateUc.size();i++){
						Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateUc.get(i);
						sb.delete(0, sb.length());
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) -  NVL(SUM(EXPENSE_AMOUNT), 0) AS B, ");
						sb.append("0 AS S, ");
						sb.append("0 AS X ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						bean.setSumIncomeAmountTypeS("0");
						bean.setSumExpenseAmount("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
							bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
							bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
						}
					}
				}
				if (type.equals("0") || type.equals("2")){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setBudgetYear(allocateYear);
					listAllocateNoUc = dao.selectList(allocateBean, " AND PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ALLOCATE_LEVEL != 0 AND ALLOCATE_CODE LIKE '%" + this.ioc + "' ORDER BY ALLOCATE_SEQ");
					for(int i=0;i<listAllocateNoUc.size();i++){
						Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateNoUc.get(i);
						sb.delete(0, sb.length());
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) -  NVL(SUM(EXPENSE_AMOUNT), 0) AS B, ");
						sb.append("0 AS S, ");
						sb.append("0 AS X ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND REASON_CODE = '00' ");
						sb.append(" GROUP BY TRANSACTION_TYPE ");
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						bean.setSumIncomeAmountTypeS("0");
						bean.setSumExpenseAmount("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
							bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
							bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
						}
					}
				}
			}*/
			int x=1;
			double sum = 0d;
			String sumIncomeAmountTypeB = "0";
			String sumIncomeAmountTypeS = "0";
			String sumExpenseAmount = "0";
			double sumUC1 = 0;
			double sumUC2 = 0;
			double sumUC3 = 0;
			double sumUC4 = 0;
			j=1;
			if (listAllocateUc.size() > 0) {
				sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 5));
				row = sheet.createRow((short)j);
				cell1 = row.createCell(0);
				cell1.setCellValue("กองทุน UC");
				for (x=0;x<listAllocateUc.size();x++){
					sumIncomeAmountTypeB = "0";
					sumIncomeAmountTypeS = "0";
					sumExpenseAmount = "0";
					row = sheet.createRow((short)j+2);
					cell1 = row.createCell(0);
					cell2 = row.createCell(1);
					cell3 = row.createCell(2);
					cell4 = row.createCell(3);
					cell5 = row.createCell(4);
					cell6 = row.createCell(5);
					cell3.setCellStyle(cellStyleAlignRigth);
					cell4.setCellStyle(cellStyleAlignRigth);
					cell5.setCellStyle(cellStyleAlignRigth);
					cell6.setCellStyle(cellStyleAlignRigth);

					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateUc.get(x);
					cell1.setCellValue(bean.getAllocateCode());
					cell2.setCellValue(bean.getAllocateName());
					if (bean.getSumIncomeAmountTypeB() == null) {
						sumIncomeAmountTypeB = "0";
					} else {
						sumIncomeAmountTypeB = bean.getSumIncomeAmountTypeB();
					}
					cell3.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumIncomeAmountTypeB), 2));
					if (bean.getSumIncomeAmountTypeS() == null) {
						sumIncomeAmountTypeS = "0";
					} else {
						sumIncomeAmountTypeS = bean.getSumIncomeAmountTypeS();
					}
					cell4.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumIncomeAmountTypeS), 2));
					if (bean.getSumExpenseAmount() == null) {
						sumExpenseAmount = "0";
					} else {
						sumExpenseAmount = bean.getSumExpenseAmount();
					}
					cell5.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumExpenseAmount), 2));
					sum = (Double.parseDouble(sumIncomeAmountTypeB)+Double.parseDouble(sumIncomeAmountTypeS)) - Double.parseDouble(sumExpenseAmount);
					cell6.setCellValue(this.digitUtil().formatDecimal(sum, 2));
					j++;
					if (bean.getAllocateLevel().equals("1")) {
						sumUC1 += Double.parseDouble(sumIncomeAmountTypeB);
						sumUC2 += Double.parseDouble(sumIncomeAmountTypeS);
						sumUC3 += Double.parseDouble(sumExpenseAmount);
						sumUC4 += sum;
					}
				}
				row = sheet.createRow((short)2);
				sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
				cell2 = row.createCell(0);
				cell3 = row.createCell(2);
				cell4 = row.createCell(3);
				cell5 = row.createCell(4);
				cell6 = row.createCell(5);
				cell2.setCellStyle(cellStyleAlignRigth);
				cell3.setCellStyle(cellStyleAlignRigth);
				cell4.setCellStyle(cellStyleAlignRigth);
				cell5.setCellStyle(cellStyleAlignRigth);
				cell6.setCellStyle(cellStyleAlignRigth);
				cell2.setCellValue("ยอดรวม");
				cell3.setCellValue(this.digitUtil().formatDecimal(sumUC1, 2));
				cell4.setCellValue(this.digitUtil().formatDecimal(sumUC2, 2));
				cell5.setCellValue(this.digitUtil().formatDecimal(sumUC3, 2));
				cell6.setCellValue(this.digitUtil().formatDecimal(sumUC4, 2));
				j++;
			}
			if (listAllocateNoUc.size() > 0) {
				//j = j + 2;
				j++;
				double sumNoUC1 = 0;
				double sumNoUC2 = 0;
				double sumNoUC3 = 0;
				double sumNoUC4 = 0;
				int ibufer = j+1;
				sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 5));
				row = sheet.createRow((short)j);
				cell1 = row.createCell(0);
				cell1.setCellValue("กองทุนไม่ใช่ UC");
				for (x=0;x<listAllocateNoUc.size();x++){
					sumIncomeAmountTypeB = "0";
					sumIncomeAmountTypeS = "0";
					sumExpenseAmount = "0";
					row = sheet.createRow((short)j+2);
					cell1 = row.createCell(0);
					cell2 = row.createCell(1);
					cell3 = row.createCell(2);
					cell4 = row.createCell(3);
					cell5 = row.createCell(4);
					cell6 = row.createCell(5);
					cell3.setCellStyle(cellStyleAlignRigth);
					cell4.setCellStyle(cellStyleAlignRigth);
					cell5.setCellStyle(cellStyleAlignRigth);
					cell6.setCellStyle(cellStyleAlignRigth);

					Acc6AllocateBean bean = (Acc6AllocateBean)listAllocateNoUc.get(x);
					cell1.setCellValue(bean.getAllocateCode());
					cell2.setCellValue(bean.getAllocateName());
					if (bean.getSumIncomeAmountTypeB() == null) {
						sumIncomeAmountTypeB = "0";
					} else {
						sumIncomeAmountTypeB = bean.getSumIncomeAmountTypeB();
					}
					cell3.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumIncomeAmountTypeB), 2));
					if (bean.getSumIncomeAmountTypeS() == null) {
						sumIncomeAmountTypeS = "0";
					} else {
						sumIncomeAmountTypeS = bean.getSumIncomeAmountTypeS();
					}
					cell4.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumIncomeAmountTypeS), 2));
					if (bean.getSumExpenseAmount() == null) {
						sumExpenseAmount = "0";
					} else {
						sumExpenseAmount = bean.getSumExpenseAmount();
					}
					cell5.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(sumExpenseAmount), 2));
					sum = (Double.parseDouble(sumIncomeAmountTypeB)+Double.parseDouble(sumIncomeAmountTypeS)) - Double.parseDouble(sumExpenseAmount);
					cell6.setCellValue(this.digitUtil().formatDecimal(sum, 2));
					j++;
					if	(bean.getAllocateLevel().equals("1")) {
						sumNoUC1 += Double.parseDouble(sumIncomeAmountTypeB);
						sumNoUC2 += Double.parseDouble(sumIncomeAmountTypeS);
						sumNoUC3 += Double.parseDouble(sumExpenseAmount);
						sumNoUC4 += sum;
					}
				}

				row = sheet.createRow((short)ibufer);
				sheet.addMergedRegion(new CellRangeAddress(ibufer, ibufer, 0, 1));
				cell2 = row.createCell(0);
				cell3 = row.createCell(2);
				cell4 = row.createCell(3);
				cell5 = row.createCell(4);
				cell6 = row.createCell(5);
				cell2.setCellStyle(cellStyleAlignRigth);
				cell3.setCellStyle(cellStyleAlignRigth);
				cell4.setCellStyle(cellStyleAlignRigth);
				cell5.setCellStyle(cellStyleAlignRigth);
				cell6.setCellStyle(cellStyleAlignRigth);
				cell2.setCellValue("ยอดรวม");
				cell3.setCellValue(this.digitUtil().formatDecimal(sumNoUC1, 2));
				cell4.setCellValue(this.digitUtil().formatDecimal(sumNoUC2, 2));
				cell5.setCellValue(this.digitUtil().formatDecimal(sumNoUC3, 2));
				cell6.setCellValue(this.digitUtil().formatDecimal(sumNoUC4, 2));
			}
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
